MYDB=$DB_PUBLIC_TRANSIT
INPUT_FOLDER=../data/raw_data/public_transit_stops
OUTPUT_FOLDER=../data/data_csv

##### Read input #####
importCSVFile $MYDB $INPUT_FOLDER/arrets2.csv arrets
sqlite3 $MYDB "delete from arrets where CodeInsee = '';"

##### Calculate aggregates #####
# Aggregate by CodeInsee
calcAggregate $MYDB 1 tmp arrets COUNT CodeInsee

# Merge with communes
equalJoin $MYDB 1 tmp2 tmp mycommunes CodeInsee

# Aggregate to canton level
sqlite3 $MYDB "create table tmp_CantonId as \
    select CantonId, SUM(mycount) as NumPublicTransitStops \
    from tmp2 group by CantonId;"

sqlite3 $MYDB "create table PublicTransitStops_CantonId as \
    select a.CantonId, NumPublicTransitStops \
    from Scope_CantonId a left join tmp_CantonId b on a.CantonId = b.CantonId"

sqlite3 $MYDB "update PublicTransitStops_CantonId set NumPublicTransitStops=0 where NumPublicTransitStops is null; "
    
dropTables $MYDB tmp_CantonId

### Clean up
dropTables $MYDB tmp tmp2 arrets
dropTables $MYDB Scope_CantonId mycommunes
sqlite3 $MYDB "VACUUM;"

